package org.lq.us.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.us.commons.Page;
import org.lq.us.dao.UsersDao;
import org.lq.us.entity.Users;
import org.lq.us.query.UsersQuery;
import org.lq.us.util.JDBCUtils;

public class UsersDaoImpl extends JDBCUtils implements UsersDao {

	@Override
	public int save(Users u) {
		String sql ="INSERT INTO `jsp`.`users`( `name`, `userName`, `password`, `gender`, `head_icon`, `rid`) VALUES (?, ?, ?, ?, ?, ?)";
		try {
			return queryRunner.update(sql,u.getName(),u.getUserName(),u.getPassword(),u.getGender(),u.getHead_icon(),u.getRid());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int update(Users u) {
		String sql ="UPDATE `jsp`.`users` SET `name` = ?, `userName` = ?, `password` = ?, `gender` = ?, `head_icon` = ?, `rid` = ? WHERE `id` = ?";
		try {
			return queryRunner.update(sql,u.getName(),u.getUserName(),u.getPassword(),u.getGender(),u.getHead_icon(),u.getRid(),u.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int delete(int id) {
		
		try {
			return queryRunner.update("delete from users where id = ?",id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}

	/**
	 *
	 */
	@Override
	public Users getById(int id) {
		try {
			return queryRunner.query("select * from users where id = ?",new BeanHandler<Users>(Users.class),id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void findPageQuery(Page<Users> page, UsersQuery uq) {
		try {
			List<Users> list = queryRunner.query("select * from users "+uq.toWhereSql(true, page), new BeanListHandler<Users>(Users.class),page.getStartIndex(),page.getPageSize());
			page.setList(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public int getCountQuery(UsersQuery uq) {
		//select count(1) from users where order by 
		try {
			return queryRunner.query("select count(1) from users "+uq.toWhereSql(false, null), new ScalarHandler<Long>()).intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public String getPassword(int id) {
		try {
			return queryRunner.query("select password from users where id = ?", new ScalarHandler<String>(),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int getByName(String name) {
		try {
			return queryRunner.query("select count(1) from users where username = ?  ", new ScalarHandler<Long>(),name).intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public Users login(String userName, String pwd) {
		try {
			return queryRunner.query("select * from users where username = ? and password = ?",new BeanHandler<Users>(Users.class),userName,pwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
